from osv import fields,osv
import mx.DateTime
import tools.sql
from tools.translate import _

class kdvn_report_contract_to_client(osv.osv):
    _name='kdvn.report.contract.to.client'
    _description='KDVN Report Contract To Client'
    _auto=False
    
    _columns={
                "contract_no":fields.char("Contract No.",size=25),
                "project_name":fields.char("Project Name",size=20),
                "description":fields.char("Payment No.",size=255),
                "contract_currency":fields.many2one('project.currency',"Contract Cur."),
                'payment_id':fields.many2one("kdvn.payment.from.client",'Payment'),
                "contract_id":fields.integer('Contract ID'),
                "quotation_info":fields.char("Payment No.",size=255),
                
                "direct_amount":fields.float("Contract Amount"),
                "vat":fields.float("Contract VAT"),
                "total_amount":fields.float("Contract Total"),
                
                "payment_number":fields.char("Payment No.",size=20),
                "claim_date":fields.date("Claim Date"),
                "claim_amount":fields.float("Claim Amount"),
                "received_date":fields.date("Received Date"),
                "received_amount":fields.float('Received Amount'),
                "red_invoice_ok":fields.boolean('VAT OK?'),
                "payment_term_line_id":fields.many2one('kdvn.payment.term.line','Payment Term Line'),
                
                "completion_date":fields.date('Completion Date'),
                "contract_sent":fields.boolean("Contract Sent?"),
                "contract_received":fields.boolean("Contract Received?"),
                
                "progress_sent":fields.boolean("Progress Sent?"),
                "progress_received":fields.boolean("Progress Received?"),
                
                "electrical":fields.float("Electrical"),
                "mechanical":fields.float("Mechanical"),
                "discount":fields.float("Discount")
              }
    def init(self,cr):
        tools.sql.drop_view_if_exists(cr,'kdvn_report_contract_to_client')
        cr.execute("create or replace view kdvn_report_contract_to_client as\
                        Select\
                            (id::text || coalesce(payment_id,0)::text)::integer as id,\
                            id as contract_id,\
                            contract_no,\
                            project_name,\
                            description,\
                            \
                            quotation_info,\
                            \
                            coalesce(contract_amount_e,0.0) + coalesce(contract_amount_m,0) as direct_amount,\
                            coalesce(contract_vat,0) as vat,\
                            coalesce(contract_total,0.0) as total_amount,\
                    \
                            number as payment_number,\
                            claim_date,\
                            claim_amount,\
                            received_date,\
                            received_amount,\
                            red_invoice_ok,\
                            payment_term_line_id,\
                            \
                            completion_date,\
                            \
                            coalesce(attached_contract_sent,False) as contract_sent,\
                            coalesce(attached_contract_received,False) as contract_received,\
                            \
                            coalesce(attached_progress_sent,False) as progress_sent,\
                            coalesce(attached_progress_received,False) as progress_received,\
                            \
                            electrical,\
                            mechanical,\
                            discount,\
                            coalesce(payment_id,0) as payment_id,\
                            pc_id as contract_currency\
                        from\
                            kdvn_contract_client kcc\
                        left join\
                                (Select id as pc_id,contract_id as pc_contract_id from project_currency where default_curr=True limit 1) pcc on kcc.id=pcc.pc_contract_id\
                        left join\
                            (Select\
                                contract_id,\
                                sum(name || to_char(date_order,': dd-Mon-yyyy;'||E'\n')) as quotation_info,\
                                sum(coalesce(q_amount_total_e,0)) as electrical,\
                                sum(coalesce(q_amount_total_m,0)) as mechanical,\
                                sum(coalesce(approved_amount,0)-coalesce(q_amount_total_e,0)-coalesce(q_amount_total_m,0)) as discount\
                            from\
                                sale_order\
                            group by\
                                contract_id) vwquotation on id=contract_id\
                        left join    \
                            (Select \
                                kpfc.id as payment_id,\
                                contract_id,\
                                number,\
                                kpfc.date as claim_date,\
                                coalesce(amount_e,0)+coalesce(amount_m,0)+coalesce(adv_payment,0)+coalesce(retention,0)+coalesce(others,0)+coalesce(kdvn_amount_tax,0) as claim_amount,\
                                max(kr.date) as received_date,\
                                sum(coalesce(tax_amount,0) + coalesce(kr.amount,0)) as received_amount,\
                                payment_term_line_id,\
                                case when coalesce(vwred.payment_id,0)=0 then False else True end as red_invoice_ok\
                            from \
                                kdvn_payment_from_client kpfc\
                            left join\
                                kdvn_received kr on kpfc.id=kr.payment_id\
                            left join\
                                (Select Distinct payment_id from payment_from_client_kdvn_red_invoice_rel where red_invoice_id>0) vwred on kpfc.id=vwred.payment_id\
                            where state not in ('draft','cancel')\
                            group by\
                                contract_id,\
                                number,\
                                kpfc.id,\
                                claim_date,\
                                claim_amount,\
                                payment_term_line_id,\
                                red_invoice_ok) vwpayment on id=vwpayment.contract_id")
kdvn_report_contract_to_client()

class kdvn_contract_client(osv.osv):
    _name = 'kdvn.contract.client'
    _inherit = 'kdvn.contract.client'
    _description="KDVN Contract Client"
    
    _columns={
              'contract_client_report_id':fields.one2many('kdvn.report.contract.to.client','id','Contract Client Report')
              } 
kdvn_contract_client()